using MediatR;
using Dapper;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.SalesManagement.H;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesManagement.H
{
    /// <summary>
    /// 导出：销售订单综合查询结果到Excel
    /// </summary>
    public class ExportSalesOrderSummaryHandler : IRequestHandler<ExportSalesOrderSummaryCommand, byte[]>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;

        public ExportSalesOrderSummaryHandler(IDbConnectionFactory dbConnectionFactory)
        {
            _dbConnectionFactory = dbConnectionFactory;
        }

        public async Task<byte[]> Handle(ExportSalesOrderSummaryCommand request, CancellationToken cancellationToken)
        {
            using var conn = _dbConnectionFactory.CreateConnection();

            var where = new List<string>();
            var p = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(request.SalesOrderNumber))
            {
                where.Add("so.OrderCode LIKE @SalesOrderNumber");
                p.Add("@SalesOrderNumber", $"%{request.SalesOrderNumber}%");
            }
            if (!string.IsNullOrWhiteSpace(request.CustomerName))
            {
                where.Add("so.CustomerName LIKE @CustomerName");
                p.Add("@CustomerName", $"%{request.CustomerName}%");
            }
            if (!string.IsNullOrWhiteSpace(request.SalesPerson))
            {
                where.Add("so.SalesPerson LIKE @SalesPerson");
                p.Add("@SalesPerson", $"%{request.SalesPerson}%");
            }
            if (!string.IsNullOrWhiteSpace(request.SalesType))
            {
                where.Add("so.SalesChannel LIKE @SalesType");
                p.Add("@SalesType", $"%{request.SalesType}%");
            }
            if (!string.IsNullOrWhiteSpace(request.MaterialName))
            {
                where.Add("m.MaterName LIKE @MaterialName");
                p.Add("@MaterialName", $"%{request.MaterialName}%");
            }

            var whereClause = where.Count > 0 ? ("WHERE " + string.Join(" AND ", where)) : string.Empty;

            var sql = $@"
SELECT 
  so.OrderCode            AS SalesOrderNumber,
  so.CustomerName         AS CustomerName,
  so.SalesPerson          AS SalesPerson,
  so.SalesChannel         AS SalesType,
  m.MaterName             AS MaterialName,
  m.MaterCode             AS MaterialCode,
  m.ModelSpec             AS ModelSpecification,
  m.Unit                  AS MainUnit,
  DATE_FORMAT(so.OrderDate, '%Y-%m-%d')    AS OrderDate,
  DATE_FORMAT(so.DeliveryDate, '%Y-%m-%d') AS DeliveryDate,
  IFNULL(so.ContractNo,'')                 AS ContractNo
FROM SalesOrder so
INNER JOIN SalesOrderDetail sod ON so.Id = sod.SalesOrderId
INNER JOIN Mater m ON sod.MaterId = m.Id
{whereClause}
ORDER BY so.OrderCode, m.MaterCode";

            var rows = (await conn.QueryAsync(sql, p)).ToList();

            // 生成Excel
            var wb = new XSSFWorkbook();
            var sheet = wb.CreateSheet("销售订单综合查询");

            var headerStyle = wb.CreateCellStyle();
            var headerFont = wb.CreateFont();
            headerFont.IsBold = true;
            headerStyle.SetFont(headerFont);

            var headers = new[]
            {
                "序号","销售订单号","客户名称","销售员","销售类型","物料名称","物料编码","型号规格","主计量","单据日期","交货日期","合同号"
            };

            var headerRow = sheet.CreateRow(0);
            for (int i = 0; i < headers.Length; i++)
            {
                var cell = headerRow.CreateCell(i);
                cell.SetCellValue(headers[i]);
                cell.CellStyle = headerStyle;
            }

            int r = 1; int serial = 1;
            foreach (dynamic d in rows)
            {
                var row = sheet.CreateRow(r++);
                int c = 0;
                row.CreateCell(c++).SetCellValue(serial++);
                row.CreateCell(c++).SetCellValue((string?)d.SalesOrderNumber ?? "");
                row.CreateCell(c++).SetCellValue((string?)d.CustomerName ?? "");
                row.CreateCell(c++).SetCellValue((string?)d.SalesPerson ?? "");
                row.CreateCell(c++).SetCellValue((string?)d.SalesType ?? "");
                row.CreateCell(c++).SetCellValue((string?)d.MaterialName ?? "");
                row.CreateCell(c++).SetCellValue((string?)d.MaterialCode ?? "");
                row.CreateCell(c++).SetCellValue((string?)d.ModelSpecification ?? "");
                row.CreateCell(c++).SetCellValue((string?)d.MainUnit ?? "");
                row.CreateCell(c++).SetCellValue((string?)d.OrderDate ?? "");
                row.CreateCell(c++).SetCellValue((string?)d.DeliveryDate ?? "");
                row.CreateCell(c++).SetCellValue((string?)d.ContractNo ?? "");
            }

            for (int i = 0; i < headers.Length; i++) sheet.AutoSizeColumn(i);

            using var ms = new MemoryStream();
            wb.Write(ms);
            return ms.ToArray();
        }
    }
}
